* Import "LALD" file in .xlsx or .dta

import excel "/Users/PathFile/LALD.xlsx", sheet("Sheet1") firstrow
use "/Users/PathFile/LALD.dta"

* Drop the variables
drop ParliamentaryTerms
drop ChamberTerms
drop ImmediateReelection
drop ConsecutiveTerms
drop ChamberSwitch

* Creating variable for ParliamentaryTerms
bysort CountryID Legislator (ElectionYear): gen ParliamentaryTerms = _n-1

* Creating variable for ChamberTerms
bysort CountryID Legislator ChamberType (ElectionYear): gen ChamberTerms = _n-1

* Creating variable for ImmediateReelection
bysort LegislatorID Country ChamberType (ElectionYear): gen ImmediateReelection = 0
replace ImmediateReelection = 1 if LegislatorID == LegislatorID[_n-1] & Country == Country[_n-1] & ChamberType == ChamberType[_n-1] & PreviousElectionYear == ElectionYear[_n-1]

* Creating variable for ConsecutiveTerms
bysort CountryID ChamberType LegislatorID (ElectionYear): gen ConsecutiveTerms = 0
replace ConsecutiveTerms = ConsecutiveTerms[_n-1] + 1 if PreviousElectionYear == ElectionYear[_n-1] & LegislatorID == LegislatorID[_n-1] & Country == Country[_n-1] & ChamberType == ChamberType[_n-1]

* Creating variable for ChamberSwitch
bysort LegislatorID Country ChamberType (ElectionYear): gen ChamberSwitch = 0
replace ChamberSwitch = 1 if LegislatorID == LegislatorID[_n-1] & Country == Country[_n-1] & ChamberType != ChamberType[_n-1] & PreviousElectionYear == ElectionYear[_n-1]

egen DistinctCountryChamber = group(ElectionYear ChamberType CountryID)

distinct DistinctCountryChamber
distinct LegislatorID
distinct ObservationID
